﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace DAL
{
    class MySQLHelper
    {
        public static string DefaultConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["mz"].ConnectionString;
            }
        }

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(DefaultConnectionString, sql, null);
        }
        public static int ExecuteNonQuery(string sql, params KeyValuePair<string, object>[] parameters)
        {
            return ExecuteNonQuery(DefaultConnectionString, sql, parameters);
        }
        public static int ExecuteNonQuery(string constr, string sql)
        {
            return ExecuteNonQuery(constr, sql, null);
        }
        public static int ExecuteNonQuery(string constr, string sql,params KeyValuePair<string,object>[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(constr))
            {
                conn.Open();

                MySqlCommand command = new MySqlCommand(sql, conn);

                if (parameters!=null&&parameters.Length>0)
                {
                    InitParameters(command, parameters);
                }

                return command.ExecuteNonQuery();
            }
        }

        #endregion

        #region ExecuteScalar
        public static string ExecuteScalar(string sql)
        {
            return ExecuteScalar(DefaultConnectionString, sql, null);
        }
        public static string ExecuteScalar(string sql, params KeyValuePair<string, object>[] parameters)
        {
            return ExecuteScalar(DefaultConnectionString, sql, parameters);
        }
        public static string ExecuteScalar(string constr, string sql)
        {
            return ExecuteScalar(constr, sql, null);
        }
        public static string ExecuteScalar(string constr, string sql, params KeyValuePair<string, object>[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(constr))
            {
                conn.Open();

                MySqlCommand command = new MySqlCommand(sql, conn);

                if (parameters != null && parameters.Length > 0)
                {
                    InitParameters(command, parameters);
                }

                return command.ExecuteScalar().ToString();
            }
        }

        #endregion

        #region Query

        public static DataTable Query(string sql)
        {
            return Query(DefaultConnectionString, sql, null);
        }
        public static DataTable Query(string sql, params KeyValuePair<string, object>[] parameters)
        {
            return Query(DefaultConnectionString, sql, parameters);
        }
        public static DataTable Query(string constr, string sql)
        {
            return Query(constr, sql, null);
        }
        public static DataTable Query(string constr, string sql, params KeyValuePair<string, object>[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(constr))
            {
                conn.Open();

                MySqlCommand command = new MySqlCommand(sql, conn);

                if (parameters != null && parameters.Length > 0)
                {
                    InitParameters(command, parameters);
                }
                MySqlDataAdapter ada = new MySqlDataAdapter(command);
                DataTable table = new DataTable();
                ada.Fill(table);
                return table;
            }
        }

        #endregion

        private static void InitParameters(MySqlCommand command, params KeyValuePair<string, object>[] parameters)
        {
            
            foreach (KeyValuePair<string, object> item in parameters)
            {
                command.Parameters.Add(item.Key, item.Value);
            }
        }
    }
}
